
USE [ESM]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Program_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Program_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Program_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets all records from the Program table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Program_Get_List

AS


				
				SELECT
					[id],
					[name],
					[periods],
					[fee]
				FROM
					[dbo].[Program]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Program_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Program_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Program_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets records from the Program table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Program_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [id]'
				SET @SQL = @SQL + ' FROM [dbo].[Program]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[id], O.[name], O.[periods], O.[fee]
				FROM
				    [dbo].[Program] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[id] = PageIndex.[id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Program]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Program_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Program_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Program_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Inserts a record into the Program table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Program_Insert
(

	@Id int    OUTPUT,

	@Name nvarchar (100)  ,

	@Periods int   ,

	@Fee int   
)
AS


				
				INSERT INTO [dbo].[Program]
					(
					[name]
					,[periods]
					,[fee]
					)
				VALUES
					(
					@Name
					,@Periods
					,@Fee
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Program_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Program_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Program_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Updates a record in the Program table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Program_Update
(

	@Id int   ,

	@Name nvarchar (100)  ,

	@Periods int   ,

	@Fee int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Program]
				SET
					[name] = @Name
					,[periods] = @Periods
					,[fee] = @Fee
				WHERE
[id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Program_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Program_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Program_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Deletes a record in the Program table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Program_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[Program] WITH (ROWLOCK) 
				WHERE
					[id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Program_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Program_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Program_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the Program table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Program_GetById
(

	@Id int   
)
AS


				SELECT
					[id],
					[name],
					[periods],
					[fee]
				FROM
					[dbo].[Program]
				WHERE
					[id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Program_GetByShiftIdFromProgramShift procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Program_GetByShiftIdFromProgramShift') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Program_GetByShiftIdFromProgramShift
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets records through a junction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Program_GetByShiftIdFromProgramShift
(

	@ShiftId int   
)
AS


SELECT dbo.[Program].[id]
       ,dbo.[Program].[name]
       ,dbo.[Program].[periods]
       ,dbo.[Program].[fee]
  FROM dbo.[Program]
 WHERE EXISTS (SELECT 1
                 FROM dbo.[ProgramShift] 
                WHERE dbo.[ProgramShift].[shiftId] = @ShiftId
                  AND dbo.[ProgramShift].[programId] = dbo.[Program].[id]
                  )
				SELECT @@ROWCOUNT			
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Program_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Program_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Program_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Finds records in the Program table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Program_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@Name nvarchar (100)  = null ,

	@Periods int   = null ,

	@Fee int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [id]
	, [name]
	, [periods]
	, [fee]
    FROM
	[dbo].[Program]
    WHERE 
	 ([id] = @Id OR @Id IS NULL)
	AND ([name] = @Name OR @Name IS NULL)
	AND ([periods] = @Periods OR @Periods IS NULL)
	AND ([fee] = @Fee OR @Fee IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [id]
	, [name]
	, [periods]
	, [fee]
    FROM
	[dbo].[Program]
    WHERE 
	 ([id] = @Id AND @Id is not null)
	OR ([name] = @Name AND @Name is not null)
	OR ([periods] = @Periods AND @Periods is not null)
	OR ([fee] = @Fee AND @Fee is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.ProgramShift_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.ProgramShift_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.ProgramShift_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets all records from the ProgramShift table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.ProgramShift_Get_List

AS


				
				SELECT
					[shiftId],
					[programId]
				FROM
					[dbo].[ProgramShift]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.ProgramShift_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.ProgramShift_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.ProgramShift_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets records from the ProgramShift table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.ProgramShift_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [shiftId] int, [programId] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([shiftId], [programId])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [shiftId], [programId]'
				SET @SQL = @SQL + ' FROM [dbo].[ProgramShift]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[shiftId], O.[programId]
				FROM
				    [dbo].[ProgramShift] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[shiftId] = PageIndex.[shiftId]
					AND O.[programId] = PageIndex.[programId]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[ProgramShift]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.ProgramShift_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.ProgramShift_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.ProgramShift_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Inserts a record into the ProgramShift table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.ProgramShift_Insert
(

	@ShiftId int   ,

	@ProgramId int   
)
AS


				
				INSERT INTO [dbo].[ProgramShift]
					(
					[shiftId]
					,[programId]
					)
				VALUES
					(
					@ShiftId
					,@ProgramId
					)
				
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.ProgramShift_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.ProgramShift_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.ProgramShift_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Updates a record in the ProgramShift table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.ProgramShift_Update
(

	@ShiftId int   ,

	@OriginalShiftId int   ,

	@ProgramId int   ,

	@OriginalProgramId int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[ProgramShift]
				SET
					[shiftId] = @ShiftId
					,[programId] = @ProgramId
				WHERE
[shiftId] = @OriginalShiftId 
AND [programId] = @OriginalProgramId 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.ProgramShift_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.ProgramShift_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.ProgramShift_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Deletes a record in the ProgramShift table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.ProgramShift_Delete
(

	@ShiftId int   ,

	@ProgramId int   
)
AS


				DELETE FROM [dbo].[ProgramShift] WITH (ROWLOCK) 
				WHERE
					[shiftId] = @ShiftId
					AND [programId] = @ProgramId
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.ProgramShift_GetByShiftId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.ProgramShift_GetByShiftId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.ProgramShift_GetByShiftId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the ProgramShift table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.ProgramShift_GetByShiftId
(

	@ShiftId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[shiftId],
					[programId]
				FROM
					[dbo].[ProgramShift]
				WHERE
					[shiftId] = @ShiftId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.ProgramShift_GetByProgramId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.ProgramShift_GetByProgramId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.ProgramShift_GetByProgramId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the ProgramShift table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.ProgramShift_GetByProgramId
(

	@ProgramId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[shiftId],
					[programId]
				FROM
					[dbo].[ProgramShift]
				WHERE
					[programId] = @ProgramId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.ProgramShift_GetByShiftIdProgramId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.ProgramShift_GetByShiftIdProgramId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.ProgramShift_GetByShiftIdProgramId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the ProgramShift table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.ProgramShift_GetByShiftIdProgramId
(

	@ShiftId int   ,

	@ProgramId int   
)
AS


				SELECT
					[shiftId],
					[programId]
				FROM
					[dbo].[ProgramShift]
				WHERE
					[shiftId] = @ShiftId
					AND [programId] = @ProgramId
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.ProgramShift_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.ProgramShift_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.ProgramShift_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Finds records in the ProgramShift table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.ProgramShift_Find
(

	@SearchUsingOR bit   = null ,

	@ShiftId int   = null ,

	@ProgramId int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [shiftId]
	, [programId]
    FROM
	[dbo].[ProgramShift]
    WHERE 
	 ([shiftId] = @ShiftId OR @ShiftId IS NULL)
	AND ([programId] = @ProgramId OR @ProgramId IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [shiftId]
	, [programId]
    FROM
	[dbo].[ProgramShift]
    WHERE 
	 ([shiftId] = @ShiftId AND @ShiftId is not null)
	OR ([programId] = @ProgramId AND @ProgramId is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Shift_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Shift_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Shift_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets all records from the Shift table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Shift_Get_List

AS


				
				SELECT
					[id],
					[name],
					[periodsInShift]
				FROM
					[dbo].[Shift]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Shift_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Shift_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Shift_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets records from the Shift table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Shift_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [id]'
				SET @SQL = @SQL + ' FROM [dbo].[Shift]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[id], O.[name], O.[periodsInShift]
				FROM
				    [dbo].[Shift] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[id] = PageIndex.[id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Shift]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Shift_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Shift_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Shift_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Inserts a record into the Shift table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Shift_Insert
(

	@Id int    OUTPUT,

	@Name nvarchar (50)  ,

	@PeriodsInShift int   
)
AS


				
				INSERT INTO [dbo].[Shift]
					(
					[name]
					,[periodsInShift]
					)
				VALUES
					(
					@Name
					,@PeriodsInShift
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Shift_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Shift_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Shift_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Updates a record in the Shift table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Shift_Update
(

	@Id int   ,

	@Name nvarchar (50)  ,

	@PeriodsInShift int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Shift]
				SET
					[name] = @Name
					,[periodsInShift] = @PeriodsInShift
				WHERE
[id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Shift_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Shift_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Shift_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Deletes a record in the Shift table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Shift_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[Shift] WITH (ROWLOCK) 
				WHERE
					[id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Shift_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Shift_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Shift_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the Shift table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Shift_GetById
(

	@Id int   
)
AS


				SELECT
					[id],
					[name],
					[periodsInShift]
				FROM
					[dbo].[Shift]
				WHERE
					[id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Shift_GetByProgramIdFromProgramShift procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Shift_GetByProgramIdFromProgramShift') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Shift_GetByProgramIdFromProgramShift
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets records through a junction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Shift_GetByProgramIdFromProgramShift
(

	@ProgramId int   
)
AS


SELECT dbo.[Shift].[id]
       ,dbo.[Shift].[name]
       ,dbo.[Shift].[periodsInShift]
  FROM dbo.[Shift]
 WHERE EXISTS (SELECT 1
                 FROM dbo.[ProgramShift] 
                WHERE dbo.[ProgramShift].[programId] = @ProgramId
                  AND dbo.[ProgramShift].[shiftId] = dbo.[Shift].[id]
                  )
				SELECT @@ROWCOUNT			
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Shift_GetByPeriodIdFromPeriodShift procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Shift_GetByPeriodIdFromPeriodShift') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Shift_GetByPeriodIdFromPeriodShift
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets records through a junction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Shift_GetByPeriodIdFromPeriodShift
(

	@PeriodId int   
)
AS


SELECT dbo.[Shift].[id]
       ,dbo.[Shift].[name]
       ,dbo.[Shift].[periodsInShift]
  FROM dbo.[Shift]
 WHERE EXISTS (SELECT 1
                 FROM dbo.[PeriodShift] 
                WHERE dbo.[PeriodShift].[periodId] = @PeriodId
                  AND dbo.[PeriodShift].[shiftId] = dbo.[Shift].[id]
                  )
				SELECT @@ROWCOUNT			
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Shift_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Shift_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Shift_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Finds records in the Shift table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Shift_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@Name nvarchar (50)  = null ,

	@PeriodsInShift int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [id]
	, [name]
	, [periodsInShift]
    FROM
	[dbo].[Shift]
    WHERE 
	 ([id] = @Id OR @Id IS NULL)
	AND ([name] = @Name OR @Name IS NULL)
	AND ([periodsInShift] = @PeriodsInShift OR @PeriodsInShift IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [id]
	, [name]
	, [periodsInShift]
    FROM
	[dbo].[Shift]
    WHERE 
	 ([id] = @Id AND @Id is not null)
	OR ([name] = @Name AND @Name is not null)
	OR ([periodsInShift] = @PeriodsInShift AND @PeriodsInShift is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Teacher_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Teacher_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Teacher_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets all records from the Teacher table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Teacher_Get_List

AS


				
				SELECT
					[accountId],
					[firstname],
					[lastname],
					[telephone],
					[email],
					[note],
					[teacherDegreeId],
					[teacherTypeId],
					[birthday],
					[code],
					[address]
				FROM
					[dbo].[Teacher]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Teacher_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Teacher_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Teacher_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets records from the Teacher table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Teacher_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [accountId] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([accountId])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [accountId]'
				SET @SQL = @SQL + ' FROM [dbo].[Teacher]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[accountId], O.[firstname], O.[lastname], O.[telephone], O.[email], O.[note], O.[teacherDegreeId], O.[teacherTypeId], O.[birthday], O.[code], O.[address]
				FROM
				    [dbo].[Teacher] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[accountId] = PageIndex.[accountId]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Teacher]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Teacher_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Teacher_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Teacher_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Inserts a record into the Teacher table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Teacher_Insert
(

	@AccountId int   ,

	@Firstname nvarchar (50)  ,

	@Lastname nvarchar (255)  ,

	@Telephone nvarchar (50)  ,

	@Email nvarchar (50)  ,

	@Note nvarchar (255)  ,

	@TeacherDegreeId int   ,

	@TeacherTypeId int   ,

	@Birthday datetime   ,

	@Code nvarchar (50)  ,

	@Address nvarchar (100)  
)
AS


				
				INSERT INTO [dbo].[Teacher]
					(
					[accountId]
					,[firstname]
					,[lastname]
					,[telephone]
					,[email]
					,[note]
					,[teacherDegreeId]
					,[teacherTypeId]
					,[birthday]
					,[code]
					,[address]
					)
				VALUES
					(
					@AccountId
					,@Firstname
					,@Lastname
					,@Telephone
					,@Email
					,@Note
					,@TeacherDegreeId
					,@TeacherTypeId
					,@Birthday
					,@Code
					,@Address
					)
				
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Teacher_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Teacher_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Teacher_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Updates a record in the Teacher table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Teacher_Update
(

	@AccountId int   ,

	@OriginalAccountId int   ,

	@Firstname nvarchar (50)  ,

	@Lastname nvarchar (255)  ,

	@Telephone nvarchar (50)  ,

	@Email nvarchar (50)  ,

	@Note nvarchar (255)  ,

	@TeacherDegreeId int   ,

	@TeacherTypeId int   ,

	@Birthday datetime   ,

	@Code nvarchar (50)  ,

	@Address nvarchar (100)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Teacher]
				SET
					[accountId] = @AccountId
					,[firstname] = @Firstname
					,[lastname] = @Lastname
					,[telephone] = @Telephone
					,[email] = @Email
					,[note] = @Note
					,[teacherDegreeId] = @TeacherDegreeId
					,[teacherTypeId] = @TeacherTypeId
					,[birthday] = @Birthday
					,[code] = @Code
					,[address] = @Address
				WHERE
[accountId] = @OriginalAccountId 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Teacher_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Teacher_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Teacher_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Deletes a record in the Teacher table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Teacher_Delete
(

	@AccountId int   
)
AS


				DELETE FROM [dbo].[Teacher] WITH (ROWLOCK) 
				WHERE
					[accountId] = @AccountId
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Teacher_GetByTeacherDegreeId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Teacher_GetByTeacherDegreeId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Teacher_GetByTeacherDegreeId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the Teacher table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Teacher_GetByTeacherDegreeId
(

	@TeacherDegreeId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[accountId],
					[firstname],
					[lastname],
					[telephone],
					[email],
					[note],
					[teacherDegreeId],
					[teacherTypeId],
					[birthday],
					[code],
					[address]
				FROM
					[dbo].[Teacher]
				WHERE
					[teacherDegreeId] = @TeacherDegreeId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Teacher_GetByAccountId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Teacher_GetByAccountId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Teacher_GetByAccountId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the Teacher table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Teacher_GetByAccountId
(

	@AccountId int   
)
AS


				SELECT
					[accountId],
					[firstname],
					[lastname],
					[telephone],
					[email],
					[note],
					[teacherDegreeId],
					[teacherTypeId],
					[birthday],
					[code],
					[address]
				FROM
					[dbo].[Teacher]
				WHERE
					[accountId] = @AccountId
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Teacher_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Teacher_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Teacher_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Finds records in the Teacher table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Teacher_Find
(

	@SearchUsingOR bit   = null ,

	@AccountId int   = null ,

	@Firstname nvarchar (50)  = null ,

	@Lastname nvarchar (255)  = null ,

	@Telephone nvarchar (50)  = null ,

	@Email nvarchar (50)  = null ,

	@Note nvarchar (255)  = null ,

	@TeacherDegreeId int   = null ,

	@TeacherTypeId int   = null ,

	@Birthday datetime   = null ,

	@Code nvarchar (50)  = null ,

	@Address nvarchar (100)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [accountId]
	, [firstname]
	, [lastname]
	, [telephone]
	, [email]
	, [note]
	, [teacherDegreeId]
	, [teacherTypeId]
	, [birthday]
	, [code]
	, [address]
    FROM
	[dbo].[Teacher]
    WHERE 
	 ([accountId] = @AccountId OR @AccountId IS NULL)
	AND ([firstname] = @Firstname OR @Firstname IS NULL)
	AND ([lastname] = @Lastname OR @Lastname IS NULL)
	AND ([telephone] = @Telephone OR @Telephone IS NULL)
	AND ([email] = @Email OR @Email IS NULL)
	AND ([note] = @Note OR @Note IS NULL)
	AND ([teacherDegreeId] = @TeacherDegreeId OR @TeacherDegreeId IS NULL)
	AND ([teacherTypeId] = @TeacherTypeId OR @TeacherTypeId IS NULL)
	AND ([birthday] = @Birthday OR @Birthday IS NULL)
	AND ([code] = @Code OR @Code IS NULL)
	AND ([address] = @Address OR @Address IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [accountId]
	, [firstname]
	, [lastname]
	, [telephone]
	, [email]
	, [note]
	, [teacherDegreeId]
	, [teacherTypeId]
	, [birthday]
	, [code]
	, [address]
    FROM
	[dbo].[Teacher]
    WHERE 
	 ([accountId] = @AccountId AND @AccountId is not null)
	OR ([firstname] = @Firstname AND @Firstname is not null)
	OR ([lastname] = @Lastname AND @Lastname is not null)
	OR ([telephone] = @Telephone AND @Telephone is not null)
	OR ([email] = @Email AND @Email is not null)
	OR ([note] = @Note AND @Note is not null)
	OR ([teacherDegreeId] = @TeacherDegreeId AND @TeacherDegreeId is not null)
	OR ([teacherTypeId] = @TeacherTypeId AND @TeacherTypeId is not null)
	OR ([birthday] = @Birthday AND @Birthday is not null)
	OR ([code] = @Code AND @Code is not null)
	OR ([address] = @Address AND @Address is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.PeriodShift_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.PeriodShift_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.PeriodShift_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets all records from the PeriodShift table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.PeriodShift_Get_List

AS


				
				SELECT
					[shiftId],
					[periodId]
				FROM
					[dbo].[PeriodShift]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.PeriodShift_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.PeriodShift_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.PeriodShift_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets records from the PeriodShift table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.PeriodShift_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [shiftId] int, [periodId] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([shiftId], [periodId])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [shiftId], [periodId]'
				SET @SQL = @SQL + ' FROM [dbo].[PeriodShift]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[shiftId], O.[periodId]
				FROM
				    [dbo].[PeriodShift] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[shiftId] = PageIndex.[shiftId]
					AND O.[periodId] = PageIndex.[periodId]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[PeriodShift]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.PeriodShift_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.PeriodShift_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.PeriodShift_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Inserts a record into the PeriodShift table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.PeriodShift_Insert
(

	@ShiftId int   ,

	@PeriodId int   
)
AS


				
				INSERT INTO [dbo].[PeriodShift]
					(
					[shiftId]
					,[periodId]
					)
				VALUES
					(
					@ShiftId
					,@PeriodId
					)
				
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.PeriodShift_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.PeriodShift_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.PeriodShift_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Updates a record in the PeriodShift table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.PeriodShift_Update
(

	@ShiftId int   ,

	@OriginalShiftId int   ,

	@PeriodId int   ,

	@OriginalPeriodId int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[PeriodShift]
				SET
					[shiftId] = @ShiftId
					,[periodId] = @PeriodId
				WHERE
[shiftId] = @OriginalShiftId 
AND [periodId] = @OriginalPeriodId 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.PeriodShift_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.PeriodShift_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.PeriodShift_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Deletes a record in the PeriodShift table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.PeriodShift_Delete
(

	@ShiftId int   ,

	@PeriodId int   
)
AS


				DELETE FROM [dbo].[PeriodShift] WITH (ROWLOCK) 
				WHERE
					[shiftId] = @ShiftId
					AND [periodId] = @PeriodId
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.PeriodShift_GetByShiftId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.PeriodShift_GetByShiftId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.PeriodShift_GetByShiftId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the PeriodShift table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.PeriodShift_GetByShiftId
(

	@ShiftId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[shiftId],
					[periodId]
				FROM
					[dbo].[PeriodShift]
				WHERE
					[shiftId] = @ShiftId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.PeriodShift_GetByPeriodId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.PeriodShift_GetByPeriodId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.PeriodShift_GetByPeriodId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the PeriodShift table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.PeriodShift_GetByPeriodId
(

	@PeriodId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[shiftId],
					[periodId]
				FROM
					[dbo].[PeriodShift]
				WHERE
					[periodId] = @PeriodId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.PeriodShift_GetByShiftIdPeriodId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.PeriodShift_GetByShiftIdPeriodId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.PeriodShift_GetByShiftIdPeriodId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the PeriodShift table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.PeriodShift_GetByShiftIdPeriodId
(

	@ShiftId int   ,

	@PeriodId int   
)
AS


				SELECT
					[shiftId],
					[periodId]
				FROM
					[dbo].[PeriodShift]
				WHERE
					[shiftId] = @ShiftId
					AND [periodId] = @PeriodId
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.PeriodShift_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.PeriodShift_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.PeriodShift_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Finds records in the PeriodShift table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.PeriodShift_Find
(

	@SearchUsingOR bit   = null ,

	@ShiftId int   = null ,

	@PeriodId int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [shiftId]
	, [periodId]
    FROM
	[dbo].[PeriodShift]
    WHERE 
	 ([shiftId] = @ShiftId OR @ShiftId IS NULL)
	AND ([periodId] = @PeriodId OR @PeriodId IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [shiftId]
	, [periodId]
    FROM
	[dbo].[PeriodShift]
    WHERE 
	 ([shiftId] = @ShiftId AND @ShiftId is not null)
	OR ([periodId] = @PeriodId AND @PeriodId is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Category_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Category_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Category_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets all records from the Category table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Category_Get_List

AS


				
				SELECT
					[id],
					[name]
				FROM
					[dbo].[Category]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Category_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Category_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Category_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets records from the Category table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Category_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [id]'
				SET @SQL = @SQL + ' FROM [dbo].[Category]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[id], O.[name]
				FROM
				    [dbo].[Category] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[id] = PageIndex.[id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Category]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Category_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Category_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Category_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Inserts a record into the Category table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Category_Insert
(

	@Id int    OUTPUT,

	@Name nvarchar (50)  
)
AS


				
				INSERT INTO [dbo].[Category]
					(
					[name]
					)
				VALUES
					(
					@Name
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Category_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Category_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Category_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Updates a record in the Category table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Category_Update
(

	@Id int   ,

	@Name nvarchar (50)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Category]
				SET
					[name] = @Name
				WHERE
[id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Category_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Category_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Category_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Deletes a record in the Category table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Category_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[Category] WITH (ROWLOCK) 
				WHERE
					[id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Category_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Category_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Category_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the Category table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Category_GetById
(

	@Id int   
)
AS


				SELECT
					[id],
					[name]
				FROM
					[dbo].[Category]
				WHERE
					[id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Category_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Category_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Category_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Finds records in the Category table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Category_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@Name nvarchar (50)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [id]
	, [name]
    FROM
	[dbo].[Category]
    WHERE 
	 ([id] = @Id OR @Id IS NULL)
	AND ([name] = @Name OR @Name IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [id]
	, [name]
    FROM
	[dbo].[Category]
    WHERE 
	 ([id] = @Id AND @Id is not null)
	OR ([name] = @Name AND @Name is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Enums_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Enums_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Enums_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets all records from the Enums table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Enums_Get_List

AS


				
				SELECT
					[id],
					[categoryId],
					[value],
					[description]
				FROM
					[dbo].[Enums]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Enums_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Enums_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Enums_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets records from the Enums table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Enums_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [id]'
				SET @SQL = @SQL + ' FROM [dbo].[Enums]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[id], O.[categoryId], O.[value], O.[description]
				FROM
				    [dbo].[Enums] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[id] = PageIndex.[id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Enums]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Enums_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Enums_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Enums_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Inserts a record into the Enums table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Enums_Insert
(

	@Id int    OUTPUT,

	@CategoryId int   ,

	@Value nvarchar (255)  ,

	@Description nvarchar (255)  
)
AS


				
				INSERT INTO [dbo].[Enums]
					(
					[categoryId]
					,[value]
					,[description]
					)
				VALUES
					(
					@CategoryId
					,@Value
					,@Description
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Enums_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Enums_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Enums_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Updates a record in the Enums table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Enums_Update
(

	@Id int   ,

	@CategoryId int   ,

	@Value nvarchar (255)  ,

	@Description nvarchar (255)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Enums]
				SET
					[categoryId] = @CategoryId
					,[value] = @Value
					,[description] = @Description
				WHERE
[id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Enums_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Enums_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Enums_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Deletes a record in the Enums table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Enums_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[Enums] WITH (ROWLOCK) 
				WHERE
					[id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Enums_GetByCategoryId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Enums_GetByCategoryId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Enums_GetByCategoryId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the Enums table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Enums_GetByCategoryId
(

	@CategoryId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[id],
					[categoryId],
					[value],
					[description]
				FROM
					[dbo].[Enums]
				WHERE
					[categoryId] = @CategoryId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Enums_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Enums_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Enums_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the Enums table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Enums_GetById
(

	@Id int   
)
AS


				SELECT
					[id],
					[categoryId],
					[value],
					[description]
				FROM
					[dbo].[Enums]
				WHERE
					[id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Enums_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Enums_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Enums_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Finds records in the Enums table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Enums_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@CategoryId int   = null ,

	@Value nvarchar (255)  = null ,

	@Description nvarchar (255)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [id]
	, [categoryId]
	, [value]
	, [description]
    FROM
	[dbo].[Enums]
    WHERE 
	 ([id] = @Id OR @Id IS NULL)
	AND ([categoryId] = @CategoryId OR @CategoryId IS NULL)
	AND ([value] = @Value OR @Value IS NULL)
	AND ([description] = @Description OR @Description IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [id]
	, [categoryId]
	, [value]
	, [description]
    FROM
	[dbo].[Enums]
    WHERE 
	 ([id] = @Id AND @Id is not null)
	OR ([categoryId] = @CategoryId AND @CategoryId is not null)
	OR ([value] = @Value AND @Value is not null)
	OR ([description] = @Description AND @Description is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Class_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Class_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Class_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets all records from the Class table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Class_Get_List

AS


				
				SELECT
					[id],
					[programId],
					[shiftId],
					[fee],
					[numberOfStudent],
					[roomId],
					[majorTeacherId],
					[minorTeacherId],
					[code],
					[openDate],
					[arrangeDate],
					[offTimes]
				FROM
					[dbo].[Class]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Class_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Class_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Class_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets records from the Class table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Class_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [id]'
				SET @SQL = @SQL + ' FROM [dbo].[Class]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[id], O.[programId], O.[shiftId], O.[fee], O.[numberOfStudent], O.[roomId], O.[majorTeacherId], O.[minorTeacherId], O.[code], O.[openDate], O.[arrangeDate], O.[offTimes]
				FROM
				    [dbo].[Class] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[id] = PageIndex.[id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Class]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Class_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Class_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Class_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Inserts a record into the Class table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Class_Insert
(

	@Id int    OUTPUT,

	@ProgramId int   ,

	@ShiftId int   ,

	@Fee money   ,

	@NumberOfStudent int   ,

	@RoomId int   ,

	@MajorTeacherId int   ,

	@MinorTeacherId int   ,

	@Code nvarchar (50)  ,

	@OpenDate datetime   ,

	@ArrangeDate datetime   ,

	@OffTimes int   
)
AS


				
				INSERT INTO [dbo].[Class]
					(
					[programId]
					,[shiftId]
					,[fee]
					,[numberOfStudent]
					,[roomId]
					,[majorTeacherId]
					,[minorTeacherId]
					,[code]
					,[openDate]
					,[arrangeDate]
					,[offTimes]
					)
				VALUES
					(
					@ProgramId
					,@ShiftId
					,@Fee
					,@NumberOfStudent
					,@RoomId
					,@MajorTeacherId
					,@MinorTeacherId
					,@Code
					,@OpenDate
					,@ArrangeDate
					,@OffTimes
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Class_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Class_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Class_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Updates a record in the Class table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Class_Update
(

	@Id int   ,

	@ProgramId int   ,

	@ShiftId int   ,

	@Fee money   ,

	@NumberOfStudent int   ,

	@RoomId int   ,

	@MajorTeacherId int   ,

	@MinorTeacherId int   ,

	@Code nvarchar (50)  ,

	@OpenDate datetime   ,

	@ArrangeDate datetime   ,

	@OffTimes int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Class]
				SET
					[programId] = @ProgramId
					,[shiftId] = @ShiftId
					,[fee] = @Fee
					,[numberOfStudent] = @NumberOfStudent
					,[roomId] = @RoomId
					,[majorTeacherId] = @MajorTeacherId
					,[minorTeacherId] = @MinorTeacherId
					,[code] = @Code
					,[openDate] = @OpenDate
					,[arrangeDate] = @ArrangeDate
					,[offTimes] = @OffTimes
				WHERE
[id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Class_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Class_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Class_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Deletes a record in the Class table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Class_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[Class] WITH (ROWLOCK) 
				WHERE
					[id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Class_GetByShiftId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Class_GetByShiftId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Class_GetByShiftId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the Class table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Class_GetByShiftId
(

	@ShiftId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[id],
					[programId],
					[shiftId],
					[fee],
					[numberOfStudent],
					[roomId],
					[majorTeacherId],
					[minorTeacherId],
					[code],
					[openDate],
					[arrangeDate],
					[offTimes]
				FROM
					[dbo].[Class]
				WHERE
					[shiftId] = @ShiftId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Class_GetByProgramId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Class_GetByProgramId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Class_GetByProgramId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the Class table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Class_GetByProgramId
(

	@ProgramId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[id],
					[programId],
					[shiftId],
					[fee],
					[numberOfStudent],
					[roomId],
					[majorTeacherId],
					[minorTeacherId],
					[code],
					[openDate],
					[arrangeDate],
					[offTimes]
				FROM
					[dbo].[Class]
				WHERE
					[programId] = @ProgramId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Class_GetByRoomId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Class_GetByRoomId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Class_GetByRoomId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the Class table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Class_GetByRoomId
(

	@RoomId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[id],
					[programId],
					[shiftId],
					[fee],
					[numberOfStudent],
					[roomId],
					[majorTeacherId],
					[minorTeacherId],
					[code],
					[openDate],
					[arrangeDate],
					[offTimes]
				FROM
					[dbo].[Class]
				WHERE
					[roomId] = @RoomId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Class_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Class_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Class_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the Class table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Class_GetById
(

	@Id int   
)
AS


				SELECT
					[id],
					[programId],
					[shiftId],
					[fee],
					[numberOfStudent],
					[roomId],
					[majorTeacherId],
					[minorTeacherId],
					[code],
					[openDate],
					[arrangeDate],
					[offTimes]
				FROM
					[dbo].[Class]
				WHERE
					[id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Class_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Class_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Class_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Finds records in the Class table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Class_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@ProgramId int   = null ,

	@ShiftId int   = null ,

	@Fee money   = null ,

	@NumberOfStudent int   = null ,

	@RoomId int   = null ,

	@MajorTeacherId int   = null ,

	@MinorTeacherId int   = null ,

	@Code nvarchar (50)  = null ,

	@OpenDate datetime   = null ,

	@ArrangeDate datetime   = null ,

	@OffTimes int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [id]
	, [programId]
	, [shiftId]
	, [fee]
	, [numberOfStudent]
	, [roomId]
	, [majorTeacherId]
	, [minorTeacherId]
	, [code]
	, [openDate]
	, [arrangeDate]
	, [offTimes]
    FROM
	[dbo].[Class]
    WHERE 
	 ([id] = @Id OR @Id IS NULL)
	AND ([programId] = @ProgramId OR @ProgramId IS NULL)
	AND ([shiftId] = @ShiftId OR @ShiftId IS NULL)
	AND ([fee] = @Fee OR @Fee IS NULL)
	AND ([numberOfStudent] = @NumberOfStudent OR @NumberOfStudent IS NULL)
	AND ([roomId] = @RoomId OR @RoomId IS NULL)
	AND ([majorTeacherId] = @MajorTeacherId OR @MajorTeacherId IS NULL)
	AND ([minorTeacherId] = @MinorTeacherId OR @MinorTeacherId IS NULL)
	AND ([code] = @Code OR @Code IS NULL)
	AND ([openDate] = @OpenDate OR @OpenDate IS NULL)
	AND ([arrangeDate] = @ArrangeDate OR @ArrangeDate IS NULL)
	AND ([offTimes] = @OffTimes OR @OffTimes IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [id]
	, [programId]
	, [shiftId]
	, [fee]
	, [numberOfStudent]
	, [roomId]
	, [majorTeacherId]
	, [minorTeacherId]
	, [code]
	, [openDate]
	, [arrangeDate]
	, [offTimes]
    FROM
	[dbo].[Class]
    WHERE 
	 ([id] = @Id AND @Id is not null)
	OR ([programId] = @ProgramId AND @ProgramId is not null)
	OR ([shiftId] = @ShiftId AND @ShiftId is not null)
	OR ([fee] = @Fee AND @Fee is not null)
	OR ([numberOfStudent] = @NumberOfStudent AND @NumberOfStudent is not null)
	OR ([roomId] = @RoomId AND @RoomId is not null)
	OR ([majorTeacherId] = @MajorTeacherId AND @MajorTeacherId is not null)
	OR ([minorTeacherId] = @MinorTeacherId AND @MinorTeacherId is not null)
	OR ([code] = @Code AND @Code is not null)
	OR ([openDate] = @OpenDate AND @OpenDate is not null)
	OR ([arrangeDate] = @ArrangeDate AND @ArrangeDate is not null)
	OR ([offTimes] = @OffTimes AND @OffTimes is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.DetailPeriod_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.DetailPeriod_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.DetailPeriod_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets all records from the DetailPeriod table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.DetailPeriod_Get_List

AS


				
				SELECT
					[classId],
					[teacherId],
					[periodId],
					[note],
					[date],
					[id]
				FROM
					[dbo].[DetailPeriod]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.DetailPeriod_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.DetailPeriod_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.DetailPeriod_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets records from the DetailPeriod table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.DetailPeriod_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [id]'
				SET @SQL = @SQL + ' FROM [dbo].[DetailPeriod]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[classId], O.[teacherId], O.[periodId], O.[note], O.[date], O.[id]
				FROM
				    [dbo].[DetailPeriod] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[id] = PageIndex.[id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[DetailPeriod]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.DetailPeriod_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.DetailPeriod_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.DetailPeriod_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Inserts a record into the DetailPeriod table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.DetailPeriod_Insert
(

	@ClassId int   ,

	@TeacherId int   ,

	@PeriodId int   ,

	@Note nvarchar (50)  ,

	@Date datetime   ,

	@Id int    OUTPUT
)
AS


				
				INSERT INTO [dbo].[DetailPeriod]
					(
					[classId]
					,[teacherId]
					,[periodId]
					,[note]
					,[date]
					)
				VALUES
					(
					@ClassId
					,@TeacherId
					,@PeriodId
					,@Note
					,@Date
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.DetailPeriod_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.DetailPeriod_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.DetailPeriod_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Updates a record in the DetailPeriod table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.DetailPeriod_Update
(

	@ClassId int   ,

	@TeacherId int   ,

	@PeriodId int   ,

	@Note nvarchar (50)  ,

	@Date datetime   ,

	@Id int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[DetailPeriod]
				SET
					[classId] = @ClassId
					,[teacherId] = @TeacherId
					,[periodId] = @PeriodId
					,[note] = @Note
					,[date] = @Date
				WHERE
[id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.DetailPeriod_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.DetailPeriod_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.DetailPeriod_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Deletes a record in the DetailPeriod table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.DetailPeriod_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[DetailPeriod] WITH (ROWLOCK) 
				WHERE
					[id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.DetailPeriod_GetByClassId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.DetailPeriod_GetByClassId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.DetailPeriod_GetByClassId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the DetailPeriod table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.DetailPeriod_GetByClassId
(

	@ClassId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[classId],
					[teacherId],
					[periodId],
					[note],
					[date],
					[id]
				FROM
					[dbo].[DetailPeriod]
				WHERE
					[classId] = @ClassId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.DetailPeriod_GetByPeriodId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.DetailPeriod_GetByPeriodId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.DetailPeriod_GetByPeriodId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the DetailPeriod table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.DetailPeriod_GetByPeriodId
(

	@PeriodId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[classId],
					[teacherId],
					[periodId],
					[note],
					[date],
					[id]
				FROM
					[dbo].[DetailPeriod]
				WHERE
					[periodId] = @PeriodId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.DetailPeriod_GetByTeacherId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.DetailPeriod_GetByTeacherId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.DetailPeriod_GetByTeacherId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the DetailPeriod table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.DetailPeriod_GetByTeacherId
(

	@TeacherId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[classId],
					[teacherId],
					[periodId],
					[note],
					[date],
					[id]
				FROM
					[dbo].[DetailPeriod]
				WHERE
					[teacherId] = @TeacherId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.DetailPeriod_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.DetailPeriod_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.DetailPeriod_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the DetailPeriod table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.DetailPeriod_GetById
(

	@Id int   
)
AS


				SELECT
					[classId],
					[teacherId],
					[periodId],
					[note],
					[date],
					[id]
				FROM
					[dbo].[DetailPeriod]
				WHERE
					[id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.DetailPeriod_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.DetailPeriod_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.DetailPeriod_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Finds records in the DetailPeriod table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.DetailPeriod_Find
(

	@SearchUsingOR bit   = null ,

	@ClassId int   = null ,

	@TeacherId int   = null ,

	@PeriodId int   = null ,

	@Note nvarchar (50)  = null ,

	@Date datetime   = null ,

	@Id int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [classId]
	, [teacherId]
	, [periodId]
	, [note]
	, [date]
	, [id]
    FROM
	[dbo].[DetailPeriod]
    WHERE 
	 ([classId] = @ClassId OR @ClassId IS NULL)
	AND ([teacherId] = @TeacherId OR @TeacherId IS NULL)
	AND ([periodId] = @PeriodId OR @PeriodId IS NULL)
	AND ([note] = @Note OR @Note IS NULL)
	AND ([date] = @Date OR @Date IS NULL)
	AND ([id] = @Id OR @Id IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [classId]
	, [teacherId]
	, [periodId]
	, [note]
	, [date]
	, [id]
    FROM
	[dbo].[DetailPeriod]
    WHERE 
	 ([classId] = @ClassId AND @ClassId is not null)
	OR ([teacherId] = @TeacherId AND @TeacherId is not null)
	OR ([periodId] = @PeriodId AND @PeriodId is not null)
	OR ([note] = @Note AND @Note is not null)
	OR ([date] = @Date AND @Date is not null)
	OR ([id] = @Id AND @Id is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Period_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Period_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Period_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets all records from the Period table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Period_Get_List

AS


				
				SELECT
					[id],
					[name],
					[startTime],
					[endTime],
					[code]
				FROM
					[dbo].[Period]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Period_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Period_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Period_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets records from the Period table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Period_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [id]'
				SET @SQL = @SQL + ' FROM [dbo].[Period]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[id], O.[name], O.[startTime], O.[endTime], O.[code]
				FROM
				    [dbo].[Period] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[id] = PageIndex.[id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Period]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Period_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Period_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Period_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Inserts a record into the Period table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Period_Insert
(

	@Id int    OUTPUT,

	@Name nvarchar (50)  ,

	@StartTime nvarchar (50)  ,

	@EndTime nvarchar (50)  ,

	@Code varchar (8)  
)
AS


				
				INSERT INTO [dbo].[Period]
					(
					[name]
					,[startTime]
					,[endTime]
					,[code]
					)
				VALUES
					(
					@Name
					,@StartTime
					,@EndTime
					,@Code
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Period_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Period_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Period_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Updates a record in the Period table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Period_Update
(

	@Id int   ,

	@Name nvarchar (50)  ,

	@StartTime nvarchar (50)  ,

	@EndTime nvarchar (50)  ,

	@Code varchar (8)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Period]
				SET
					[name] = @Name
					,[startTime] = @StartTime
					,[endTime] = @EndTime
					,[code] = @Code
				WHERE
[id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Period_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Period_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Period_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Deletes a record in the Period table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Period_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[Period] WITH (ROWLOCK) 
				WHERE
					[id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Period_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Period_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Period_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the Period table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Period_GetById
(

	@Id int   
)
AS


				SELECT
					[id],
					[name],
					[startTime],
					[endTime],
					[code]
				FROM
					[dbo].[Period]
				WHERE
					[id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Period_GetBySessionIdFromSessionPeriod procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Period_GetBySessionIdFromSessionPeriod') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Period_GetBySessionIdFromSessionPeriod
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets records through a junction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Period_GetBySessionIdFromSessionPeriod
(

	@SessionId int   
)
AS


SELECT dbo.[Period].[id]
       ,dbo.[Period].[name]
       ,dbo.[Period].[startTime]
       ,dbo.[Period].[endTime]
       ,dbo.[Period].[code]
  FROM dbo.[Period]
 WHERE EXISTS (SELECT 1
                 FROM dbo.[SessionPeriod] 
                WHERE dbo.[SessionPeriod].[sessionId] = @SessionId
                  AND dbo.[SessionPeriod].[periodId] = dbo.[Period].[id]
                  )
				SELECT @@ROWCOUNT			
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Period_GetByShiftIdFromPeriodShift procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Period_GetByShiftIdFromPeriodShift') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Period_GetByShiftIdFromPeriodShift
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets records through a junction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Period_GetByShiftIdFromPeriodShift
(

	@ShiftId int   
)
AS


SELECT dbo.[Period].[id]
       ,dbo.[Period].[name]
       ,dbo.[Period].[startTime]
       ,dbo.[Period].[endTime]
       ,dbo.[Period].[code]
  FROM dbo.[Period]
 WHERE EXISTS (SELECT 1
                 FROM dbo.[PeriodShift] 
                WHERE dbo.[PeriodShift].[shiftId] = @ShiftId
                  AND dbo.[PeriodShift].[periodId] = dbo.[Period].[id]
                  )
				SELECT @@ROWCOUNT			
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Period_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Period_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Period_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Finds records in the Period table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Period_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@Name nvarchar (50)  = null ,

	@StartTime nvarchar (50)  = null ,

	@EndTime nvarchar (50)  = null ,

	@Code varchar (8)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [id]
	, [name]
	, [startTime]
	, [endTime]
	, [code]
    FROM
	[dbo].[Period]
    WHERE 
	 ([id] = @Id OR @Id IS NULL)
	AND ([name] = @Name OR @Name IS NULL)
	AND ([startTime] = @StartTime OR @StartTime IS NULL)
	AND ([endTime] = @EndTime OR @EndTime IS NULL)
	AND ([code] = @Code OR @Code IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [id]
	, [name]
	, [startTime]
	, [endTime]
	, [code]
    FROM
	[dbo].[Period]
    WHERE 
	 ([id] = @Id AND @Id is not null)
	OR ([name] = @Name AND @Name is not null)
	OR ([startTime] = @StartTime AND @StartTime is not null)
	OR ([endTime] = @EndTime AND @EndTime is not null)
	OR ([code] = @Code AND @Code is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Student_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Student_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Student_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets all records from the Student table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Student_Get_List

AS


				
				SELECT
					[id],
					[firstname],
					[lastname],
					[email],
					[telephone],
					[numberReceipt],
					[isReservated],
					[classId],
					[personId],
					[note],
					[inScore],
					[outScore],
					[registeredDate]
				FROM
					[dbo].[Student]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Student_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Student_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Student_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets records from the Student table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Student_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [id]'
				SET @SQL = @SQL + ' FROM [dbo].[Student]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[id], O.[firstname], O.[lastname], O.[email], O.[telephone], O.[numberReceipt], O.[isReservated], O.[classId], O.[personId], O.[note], O.[inScore], O.[outScore], O.[registeredDate]
				FROM
				    [dbo].[Student] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[id] = PageIndex.[id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Student]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Student_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Student_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Student_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Inserts a record into the Student table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Student_Insert
(

	@Id int    OUTPUT,

	@Firstname nvarchar (50)  ,

	@Lastname nvarchar (255)  ,

	@Email nvarchar (50)  ,

	@Telephone nvarchar (50)  ,

	@NumberReceipt nvarchar (50)  ,

	@IsReservated bit   ,

	@ClassId int   ,

	@PersonId int   ,

	@Note nvarchar (250)  ,

	@InScore float   ,

	@OutScore float   ,

	@RegisteredDate datetime   
)
AS


				
				INSERT INTO [dbo].[Student]
					(
					[firstname]
					,[lastname]
					,[email]
					,[telephone]
					,[numberReceipt]
					,[isReservated]
					,[classId]
					,[personId]
					,[note]
					,[inScore]
					,[outScore]
					,[registeredDate]
					)
				VALUES
					(
					@Firstname
					,@Lastname
					,@Email
					,@Telephone
					,@NumberReceipt
					,@IsReservated
					,@ClassId
					,@PersonId
					,@Note
					,@InScore
					,@OutScore
					,@RegisteredDate
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Student_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Student_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Student_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Updates a record in the Student table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Student_Update
(

	@Id int   ,

	@Firstname nvarchar (50)  ,

	@Lastname nvarchar (255)  ,

	@Email nvarchar (50)  ,

	@Telephone nvarchar (50)  ,

	@NumberReceipt nvarchar (50)  ,

	@IsReservated bit   ,

	@ClassId int   ,

	@PersonId int   ,

	@Note nvarchar (250)  ,

	@InScore float   ,

	@OutScore float   ,

	@RegisteredDate datetime   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Student]
				SET
					[firstname] = @Firstname
					,[lastname] = @Lastname
					,[email] = @Email
					,[telephone] = @Telephone
					,[numberReceipt] = @NumberReceipt
					,[isReservated] = @IsReservated
					,[classId] = @ClassId
					,[personId] = @PersonId
					,[note] = @Note
					,[inScore] = @InScore
					,[outScore] = @OutScore
					,[registeredDate] = @RegisteredDate
				WHERE
[id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Student_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Student_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Student_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Deletes a record in the Student table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Student_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[Student] WITH (ROWLOCK) 
				WHERE
					[id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Student_GetByPersonId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Student_GetByPersonId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Student_GetByPersonId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the Student table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Student_GetByPersonId
(

	@PersonId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[id],
					[firstname],
					[lastname],
					[email],
					[telephone],
					[numberReceipt],
					[isReservated],
					[classId],
					[personId],
					[note],
					[inScore],
					[outScore],
					[registeredDate]
				FROM
					[dbo].[Student]
				WHERE
					[personId] = @PersonId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Student_GetByClassId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Student_GetByClassId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Student_GetByClassId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the Student table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Student_GetByClassId
(

	@ClassId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[id],
					[firstname],
					[lastname],
					[email],
					[telephone],
					[numberReceipt],
					[isReservated],
					[classId],
					[personId],
					[note],
					[inScore],
					[outScore],
					[registeredDate]
				FROM
					[dbo].[Student]
				WHERE
					[classId] = @ClassId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Student_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Student_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Student_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the Student table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Student_GetById
(

	@Id int   
)
AS


				SELECT
					[id],
					[firstname],
					[lastname],
					[email],
					[telephone],
					[numberReceipt],
					[isReservated],
					[classId],
					[personId],
					[note],
					[inScore],
					[outScore],
					[registeredDate]
				FROM
					[dbo].[Student]
				WHERE
					[id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Student_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Student_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Student_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Finds records in the Student table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Student_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@Firstname nvarchar (50)  = null ,

	@Lastname nvarchar (255)  = null ,

	@Email nvarchar (50)  = null ,

	@Telephone nvarchar (50)  = null ,

	@NumberReceipt nvarchar (50)  = null ,

	@IsReservated bit   = null ,

	@ClassId int   = null ,

	@PersonId int   = null ,

	@Note nvarchar (250)  = null ,

	@InScore float   = null ,

	@OutScore float   = null ,

	@RegisteredDate datetime   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [id]
	, [firstname]
	, [lastname]
	, [email]
	, [telephone]
	, [numberReceipt]
	, [isReservated]
	, [classId]
	, [personId]
	, [note]
	, [inScore]
	, [outScore]
	, [registeredDate]
    FROM
	[dbo].[Student]
    WHERE 
	 ([id] = @Id OR @Id IS NULL)
	AND ([firstname] = @Firstname OR @Firstname IS NULL)
	AND ([lastname] = @Lastname OR @Lastname IS NULL)
	AND ([email] = @Email OR @Email IS NULL)
	AND ([telephone] = @Telephone OR @Telephone IS NULL)
	AND ([numberReceipt] = @NumberReceipt OR @NumberReceipt IS NULL)
	AND ([isReservated] = @IsReservated OR @IsReservated IS NULL)
	AND ([classId] = @ClassId OR @ClassId IS NULL)
	AND ([personId] = @PersonId OR @PersonId IS NULL)
	AND ([note] = @Note OR @Note IS NULL)
	AND ([inScore] = @InScore OR @InScore IS NULL)
	AND ([outScore] = @OutScore OR @OutScore IS NULL)
	AND ([registeredDate] = @RegisteredDate OR @RegisteredDate IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [id]
	, [firstname]
	, [lastname]
	, [email]
	, [telephone]
	, [numberReceipt]
	, [isReservated]
	, [classId]
	, [personId]
	, [note]
	, [inScore]
	, [outScore]
	, [registeredDate]
    FROM
	[dbo].[Student]
    WHERE 
	 ([id] = @Id AND @Id is not null)
	OR ([firstname] = @Firstname AND @Firstname is not null)
	OR ([lastname] = @Lastname AND @Lastname is not null)
	OR ([email] = @Email AND @Email is not null)
	OR ([telephone] = @Telephone AND @Telephone is not null)
	OR ([numberReceipt] = @NumberReceipt AND @NumberReceipt is not null)
	OR ([isReservated] = @IsReservated AND @IsReservated is not null)
	OR ([classId] = @ClassId AND @ClassId is not null)
	OR ([personId] = @PersonId AND @PersonId is not null)
	OR ([note] = @Note AND @Note is not null)
	OR ([inScore] = @InScore AND @InScore is not null)
	OR ([outScore] = @OutScore AND @OutScore is not null)
	OR ([registeredDate] = @RegisteredDate AND @RegisteredDate is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Account_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Account_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Account_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets all records from the Account table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Account_Get_List

AS


				
				SELECT
					[id],
					[accountName],
					[accountPass],
					[userLevelId]
				FROM
					[dbo].[Account]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Account_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Account_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Account_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets records from the Account table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Account_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [id]'
				SET @SQL = @SQL + ' FROM [dbo].[Account]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[id], O.[accountName], O.[accountPass], O.[userLevelId]
				FROM
				    [dbo].[Account] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[id] = PageIndex.[id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Account]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Account_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Account_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Account_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Inserts a record into the Account table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Account_Insert
(

	@Id int    OUTPUT,

	@AccountName varchar (50)  ,

	@AccountPass varchar (50)  ,

	@UserLevelId int   
)
AS


				
				INSERT INTO [dbo].[Account]
					(
					[accountName]
					,[accountPass]
					,[userLevelId]
					)
				VALUES
					(
					@AccountName
					,@AccountPass
					,@UserLevelId
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Account_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Account_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Account_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Updates a record in the Account table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Account_Update
(

	@Id int   ,

	@AccountName varchar (50)  ,

	@AccountPass varchar (50)  ,

	@UserLevelId int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Account]
				SET
					[accountName] = @AccountName
					,[accountPass] = @AccountPass
					,[userLevelId] = @UserLevelId
				WHERE
[id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Account_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Account_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Account_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Deletes a record in the Account table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Account_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[Account] WITH (ROWLOCK) 
				WHERE
					[id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Account_GetByUserLevelId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Account_GetByUserLevelId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Account_GetByUserLevelId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the Account table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Account_GetByUserLevelId
(

	@UserLevelId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[id],
					[accountName],
					[accountPass],
					[userLevelId]
				FROM
					[dbo].[Account]
				WHERE
					[userLevelId] = @UserLevelId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Account_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Account_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Account_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the Account table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Account_GetById
(

	@Id int   
)
AS


				SELECT
					[id],
					[accountName],
					[accountPass],
					[userLevelId]
				FROM
					[dbo].[Account]
				WHERE
					[id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Account_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Account_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Account_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Finds records in the Account table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Account_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@AccountName varchar (50)  = null ,

	@AccountPass varchar (50)  = null ,

	@UserLevelId int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [id]
	, [accountName]
	, [accountPass]
	, [userLevelId]
    FROM
	[dbo].[Account]
    WHERE 
	 ([id] = @Id OR @Id IS NULL)
	AND ([accountName] = @AccountName OR @AccountName IS NULL)
	AND ([accountPass] = @AccountPass OR @AccountPass IS NULL)
	AND ([userLevelId] = @UserLevelId OR @UserLevelId IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [id]
	, [accountName]
	, [accountPass]
	, [userLevelId]
    FROM
	[dbo].[Account]
    WHERE 
	 ([id] = @Id AND @Id is not null)
	OR ([accountName] = @AccountName AND @AccountName is not null)
	OR ([accountPass] = @AccountPass AND @AccountPass is not null)
	OR ([userLevelId] = @UserLevelId AND @UserLevelId is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Session_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Session_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Session_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets all records from the Session table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Session_Get_List

AS


				
				SELECT
					[id],
					[name]
				FROM
					[dbo].[Session]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Session_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Session_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Session_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets records from the Session table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Session_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [id]'
				SET @SQL = @SQL + ' FROM [dbo].[Session]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[id], O.[name]
				FROM
				    [dbo].[Session] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[id] = PageIndex.[id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[Session]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Session_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Session_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Session_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Inserts a record into the Session table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Session_Insert
(

	@Id int    OUTPUT,

	@Name nvarchar (50)  
)
AS


				
				INSERT INTO [dbo].[Session]
					(
					[name]
					)
				VALUES
					(
					@Name
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Session_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Session_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Session_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Updates a record in the Session table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Session_Update
(

	@Id int   ,

	@Name nvarchar (50)  
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[Session]
				SET
					[name] = @Name
				WHERE
[id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Session_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Session_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Session_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Deletes a record in the Session table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Session_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[Session] WITH (ROWLOCK) 
				WHERE
					[id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Session_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Session_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Session_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the Session table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Session_GetById
(

	@Id int   
)
AS


				SELECT
					[id],
					[name]
				FROM
					[dbo].[Session]
				WHERE
					[id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Session_GetByPeriodIdFromSessionPeriod procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Session_GetByPeriodIdFromSessionPeriod') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Session_GetByPeriodIdFromSessionPeriod
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets records through a junction table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Session_GetByPeriodIdFromSessionPeriod
(

	@PeriodId int   
)
AS


SELECT dbo.[Session].[id]
       ,dbo.[Session].[name]
  FROM dbo.[Session]
 WHERE EXISTS (SELECT 1
                 FROM dbo.[SessionPeriod] 
                WHERE dbo.[SessionPeriod].[periodId] = @PeriodId
                  AND dbo.[SessionPeriod].[sessionId] = dbo.[Session].[id]
                  )
				SELECT @@ROWCOUNT			
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.Session_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.Session_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.Session_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Finds records in the Session table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.Session_Find
(

	@SearchUsingOR bit   = null ,

	@Id int   = null ,

	@Name nvarchar (50)  = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [id]
	, [name]
    FROM
	[dbo].[Session]
    WHERE 
	 ([id] = @Id OR @Id IS NULL)
	AND ([name] = @Name OR @Name IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [id]
	, [name]
    FROM
	[dbo].[Session]
    WHERE 
	 ([id] = @Id AND @Id is not null)
	OR ([name] = @Name AND @Name is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.SessionPeriod_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.SessionPeriod_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.SessionPeriod_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets all records from the SessionPeriod table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.SessionPeriod_Get_List

AS


				
				SELECT
					[periodId],
					[sessionId]
				FROM
					[dbo].[SessionPeriod]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.SessionPeriod_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.SessionPeriod_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.SessionPeriod_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets records from the SessionPeriod table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.SessionPeriod_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [periodId] int, [sessionId] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([periodId], [sessionId])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [periodId], [sessionId]'
				SET @SQL = @SQL + ' FROM [dbo].[SessionPeriod]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[periodId], O.[sessionId]
				FROM
				    [dbo].[SessionPeriod] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[periodId] = PageIndex.[periodId]
					AND O.[sessionId] = PageIndex.[sessionId]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[SessionPeriod]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.SessionPeriod_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.SessionPeriod_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.SessionPeriod_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Inserts a record into the SessionPeriod table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.SessionPeriod_Insert
(

	@PeriodId int   ,

	@SessionId int   
)
AS


				
				INSERT INTO [dbo].[SessionPeriod]
					(
					[periodId]
					,[sessionId]
					)
				VALUES
					(
					@PeriodId
					,@SessionId
					)
				
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.SessionPeriod_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.SessionPeriod_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.SessionPeriod_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Updates a record in the SessionPeriod table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.SessionPeriod_Update
(

	@PeriodId int   ,

	@OriginalPeriodId int   ,

	@SessionId int   ,

	@OriginalSessionId int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[SessionPeriod]
				SET
					[periodId] = @PeriodId
					,[sessionId] = @SessionId
				WHERE
[periodId] = @OriginalPeriodId 
AND [sessionId] = @OriginalSessionId 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.SessionPeriod_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.SessionPeriod_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.SessionPeriod_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Deletes a record in the SessionPeriod table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.SessionPeriod_Delete
(

	@PeriodId int   ,

	@SessionId int   
)
AS


				DELETE FROM [dbo].[SessionPeriod] WITH (ROWLOCK) 
				WHERE
					[periodId] = @PeriodId
					AND [sessionId] = @SessionId
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.SessionPeriod_GetByPeriodId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.SessionPeriod_GetByPeriodId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.SessionPeriod_GetByPeriodId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the SessionPeriod table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.SessionPeriod_GetByPeriodId
(

	@PeriodId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[periodId],
					[sessionId]
				FROM
					[dbo].[SessionPeriod]
				WHERE
					[periodId] = @PeriodId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.SessionPeriod_GetBySessionId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.SessionPeriod_GetBySessionId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.SessionPeriod_GetBySessionId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the SessionPeriod table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.SessionPeriod_GetBySessionId
(

	@SessionId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[periodId],
					[sessionId]
				FROM
					[dbo].[SessionPeriod]
				WHERE
					[sessionId] = @SessionId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.SessionPeriod_GetByPeriodIdSessionId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.SessionPeriod_GetByPeriodIdSessionId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.SessionPeriod_GetByPeriodIdSessionId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the SessionPeriod table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.SessionPeriod_GetByPeriodIdSessionId
(

	@PeriodId int   ,

	@SessionId int   
)
AS


				SELECT
					[periodId],
					[sessionId]
				FROM
					[dbo].[SessionPeriod]
				WHERE
					[periodId] = @PeriodId
					AND [sessionId] = @SessionId
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.SessionPeriod_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.SessionPeriod_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.SessionPeriod_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Finds records in the SessionPeriod table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.SessionPeriod_Find
(

	@SearchUsingOR bit   = null ,

	@PeriodId int   = null ,

	@SessionId int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [periodId]
	, [sessionId]
    FROM
	[dbo].[SessionPeriod]
    WHERE 
	 ([periodId] = @PeriodId OR @PeriodId IS NULL)
	AND ([sessionId] = @SessionId OR @SessionId IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [periodId]
	, [sessionId]
    FROM
	[dbo].[SessionPeriod]
    WHERE 
	 ([periodId] = @PeriodId AND @PeriodId is not null)
	OR ([sessionId] = @SessionId AND @SessionId is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.DetailMissPeriod_Get_List procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.DetailMissPeriod_Get_List') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.DetailMissPeriod_Get_List
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets all records from the DetailMissPeriod table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.DetailMissPeriod_Get_List

AS


				
				SELECT
					[classId],
					[teacherId],
					[periodId],
					[note],
					[date],
					[id]
				FROM
					[dbo].[DetailMissPeriod]
					
				SELECT @@ROWCOUNT
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.DetailMissPeriod_GetPaged procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.DetailMissPeriod_GetPaged') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.DetailMissPeriod_GetPaged
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Gets records from the DetailMissPeriod table passing page index and page count parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.DetailMissPeriod_GetPaged
(

	@WhereClause varchar (2000)  ,

	@OrderBy varchar (2000)  ,

	@PageIndex int   ,

	@PageSize int   
)
AS


				
				BEGIN
				DECLARE @PageLowerBound int
				DECLARE @PageUpperBound int
				
				-- Set the page bounds
				SET @PageLowerBound = @PageSize * @PageIndex
				SET @PageUpperBound = @PageLowerBound + @PageSize

				-- Create a temp table to store the select results
				CREATE TABLE #PageIndex
				(
				    [IndexId] int IDENTITY (1, 1) NOT NULL,
				    [id] int 
				)
				
				-- Insert into the temp table
				DECLARE @SQL AS nvarchar(4000)
				SET @SQL = 'INSERT INTO #PageIndex ([id])'
				SET @SQL = @SQL + ' SELECT'
				IF @PageSize > 0
				BEGIN
					SET @SQL = @SQL + ' TOP ' + CONVERT(nvarchar, @PageUpperBound)
				END
				SET @SQL = @SQL + ' [id]'
				SET @SQL = @SQL + ' FROM [dbo].[DetailMissPeriod]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				IF LEN(@OrderBy) > 0
				BEGIN
					SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
				END
				
				-- Populate the temp table
				EXEC sp_executesql @SQL

				-- Return paged results
				SELECT O.[classId], O.[teacherId], O.[periodId], O.[note], O.[date], O.[id]
				FROM
				    [dbo].[DetailMissPeriod] O,
				    #PageIndex PageIndex
				WHERE
				    PageIndex.IndexId > @PageLowerBound
					AND O.[id] = PageIndex.[id]
				ORDER BY
				    PageIndex.IndexId
				
				-- get row count
				SET @SQL = 'SELECT COUNT(*) AS TotalRowCount'
				SET @SQL = @SQL + ' FROM [dbo].[DetailMissPeriod]'
				IF LEN(@WhereClause) > 0
				BEGIN
					SET @SQL = @SQL + ' WHERE ' + @WhereClause
				END
				EXEC sp_executesql @SQL
			
				END
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.DetailMissPeriod_Insert procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.DetailMissPeriod_Insert') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.DetailMissPeriod_Insert
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Inserts a record into the DetailMissPeriod table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.DetailMissPeriod_Insert
(

	@ClassId int   ,

	@TeacherId int   ,

	@PeriodId int   ,

	@Note nvarchar (50)  ,

	@Date datetime   ,

	@Id int    OUTPUT
)
AS


				
				INSERT INTO [dbo].[DetailMissPeriod]
					(
					[classId]
					,[teacherId]
					,[periodId]
					,[note]
					,[date]
					)
				VALUES
					(
					@ClassId
					,@TeacherId
					,@PeriodId
					,@Note
					,@Date
					)
				
				-- Get the identity value
				SET @Id = SCOPE_IDENTITY()
									
							
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.DetailMissPeriod_Update procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.DetailMissPeriod_Update') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.DetailMissPeriod_Update
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Updates a record in the DetailMissPeriod table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.DetailMissPeriod_Update
(

	@ClassId int   ,

	@TeacherId int   ,

	@PeriodId int   ,

	@Note nvarchar (50)  ,

	@Date datetime   ,

	@Id int   
)
AS


				
				
				-- Modify the updatable columns
				UPDATE
					[dbo].[DetailMissPeriod]
				SET
					[classId] = @ClassId
					,[teacherId] = @TeacherId
					,[periodId] = @PeriodId
					,[note] = @Note
					,[date] = @Date
				WHERE
[id] = @Id 
				
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.DetailMissPeriod_Delete procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.DetailMissPeriod_Delete') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.DetailMissPeriod_Delete
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Deletes a record in the DetailMissPeriod table
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.DetailMissPeriod_Delete
(

	@Id int   
)
AS


				DELETE FROM [dbo].[DetailMissPeriod] WITH (ROWLOCK) 
				WHERE
					[id] = @Id
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.DetailMissPeriod_GetByPeriodId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.DetailMissPeriod_GetByPeriodId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.DetailMissPeriod_GetByPeriodId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the DetailMissPeriod table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.DetailMissPeriod_GetByPeriodId
(

	@PeriodId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[classId],
					[teacherId],
					[periodId],
					[note],
					[date],
					[id]
				FROM
					[dbo].[DetailMissPeriod]
				WHERE
					[periodId] = @PeriodId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.DetailMissPeriod_GetByClassId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.DetailMissPeriod_GetByClassId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.DetailMissPeriod_GetByClassId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the DetailMissPeriod table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.DetailMissPeriod_GetByClassId
(

	@ClassId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[classId],
					[teacherId],
					[periodId],
					[note],
					[date],
					[id]
				FROM
					[dbo].[DetailMissPeriod]
				WHERE
					[classId] = @ClassId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.DetailMissPeriod_GetByTeacherId procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.DetailMissPeriod_GetByTeacherId') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.DetailMissPeriod_GetByTeacherId
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the DetailMissPeriod table through a foreign key
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.DetailMissPeriod_GetByTeacherId
(

	@TeacherId int   
)
AS


				SET ANSI_NULLS OFF
				
				SELECT
					[classId],
					[teacherId],
					[periodId],
					[note],
					[date],
					[id]
				FROM
					[dbo].[DetailMissPeriod]
				WHERE
					[teacherId] = @TeacherId
				
				SELECT @@ROWCOUNT
				SET ANSI_NULLS ON
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.DetailMissPeriod_GetById procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.DetailMissPeriod_GetById') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.DetailMissPeriod_GetById
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Select records from the DetailMissPeriod table through an index
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.DetailMissPeriod_GetById
(

	@Id int   
)
AS


				SELECT
					[classId],
					[teacherId],
					[periodId],
					[note],
					[date],
					[id]
				FROM
					[dbo].[DetailMissPeriod]
				WHERE
					[id] = @Id
				SELECT @@ROWCOUNT
					
			

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

	

-- Drop the dbo.DetailMissPeriod_Find procedure
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'dbo.DetailMissPeriod_Find') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.DetailMissPeriod_Find
GO

/*
----------------------------------------------------------------------------------------------------

-- Created By: InfoExpress ()
-- Purpose: Finds records in the DetailMissPeriod table passing nullable parameters
----------------------------------------------------------------------------------------------------
*/


CREATE PROCEDURE dbo.DetailMissPeriod_Find
(

	@SearchUsingOR bit   = null ,

	@ClassId int   = null ,

	@TeacherId int   = null ,

	@PeriodId int   = null ,

	@Note nvarchar (50)  = null ,

	@Date datetime   = null ,

	@Id int   = null 
)
AS


				
  IF ISNULL(@SearchUsingOR, 0) <> 1
  BEGIN
    SELECT
	  [classId]
	, [teacherId]
	, [periodId]
	, [note]
	, [date]
	, [id]
    FROM
	[dbo].[DetailMissPeriod]
    WHERE 
	 ([classId] = @ClassId OR @ClassId IS NULL)
	AND ([teacherId] = @TeacherId OR @TeacherId IS NULL)
	AND ([periodId] = @PeriodId OR @PeriodId IS NULL)
	AND ([note] = @Note OR @Note IS NULL)
	AND ([date] = @Date OR @Date IS NULL)
	AND ([id] = @Id OR @Id IS NULL)
						
  END
  ELSE
  BEGIN
    SELECT
	  [classId]
	, [teacherId]
	, [periodId]
	, [note]
	, [date]
	, [id]
    FROM
	[dbo].[DetailMissPeriod]
    WHERE 
	 ([classId] = @ClassId AND @ClassId is not null)
	OR ([teacherId] = @TeacherId AND @TeacherId is not null)
	OR ([periodId] = @PeriodId AND @PeriodId is not null)
	OR ([note] = @Note AND @Note is not null)
	OR ([date] = @Date AND @Date is not null)
	OR ([id] = @Id AND @Id is not null)
	SELECT @@ROWCOUNT			
  END
				

GO
SET QUOTED_IDENTIFIER ON 
GO
SET NOCOUNT ON
GO
SET ANSI_NULLS OFF 
GO

